package kr.co.handysoft.common.vo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import kr.co.handysoft.concurrent.VehicleCollectionProcessor;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

public class DatabaseInformation {

	private String id;
	private String passwd;
	private String database;
	private String sid;
	private int idleCnt;
	private int maxConn;
	private String url;
	private PoolableConnectionFactory factory;
	private PoolingDataSource dSource;
	
	public DatabaseInformation() {
		super();
		// TODO Auto-generated constructor stub
	}

	public DatabaseInformation(String id, String passwd, String database, String sid, int idleCnt, int maxConn, String url) {
		super();
		this.id = id;
		this.passwd = passwd;
		this.database = database;
		this.sid = sid;
		this.idleCnt = idleCnt;
		this.maxConn = maxConn;
		this.url = url;
		
		setupDataSource(this.url, this.id, this.passwd, this.idleCnt, this.maxConn);
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getPasswd() {
		return passwd;
	}

	public void setPasswd(String passwd) {
		this.passwd = passwd;
	}

	public String getDatabase() {
		return database;
	}

	public void setDatabase(String database) {
		this.database = database;
	}

	public String getSid() {
		return sid;
	}

	public void setSid(String sid) {
		this.sid = sid;
	}

	public int getIdleCnt() {
		return idleCnt;
	}

	public void setIdleCnt(int idleCnt) {
		this.idleCnt = idleCnt;
	}

	public int getMaxConn() {
		return maxConn;
	}

	public void setMaxConn(int maxConn) {
		this.maxConn = maxConn;
	}

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public void getData(int levelKey, String keyString, String queryString) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		
		try {
			
			conn = getConnection();
			pstmt = conn.prepareStatement(queryString);
			rset = pstmt.executeQuery();
			
			while(rset.next()) {
				
				String vehicleNumber = rset.getString(keyString);
				Vehicle vehicle = new Vehicle(vehicleNumber);
				VehicleCollectionProcessor.addVehicle(levelKey, vehicle);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
	        if (rset != null) try { rset.close(); } catch(SQLException ex) {}
	        if (pstmt != null) try { pstmt.close(); } catch(SQLException ex) {}
	        if (conn != null) try { conn.close(); } catch(SQLException ex) {}
	    }
				
	}

	public void addData(int levelKey, Vehicle vehicle, String queryString) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			
			conn = getConnection();
			pstmt = conn.prepareStatement(queryString);
			pstmt.executeUpdate();

			VehicleCollectionProcessor.addVehicle(levelKey, vehicle);
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
	        if (pstmt != null) try { pstmt.close(); } catch(SQLException ex) {}
	        if (conn != null) try { conn.close(); } catch(SQLException ex) {}
	    }
				
	}

	public void delData(int levelKey, Vehicle vehicle, String queryString) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			
			conn = getConnection();
			pstmt = conn.prepareStatement(queryString);
			pstmt.executeUpdate();

			VehicleCollectionProcessor.removeVehicle(levelKey, vehicle.getNumber());
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
	        if (pstmt != null) try { pstmt.close(); } catch(SQLException ex) {}
	        if (conn != null) try { conn.close(); } catch(SQLException ex) {}
	    }
				
	}
	
	public void setupDataSource(String connectUri, String username, String password, int minIdle, int maxActive) {
		GenericObjectPool<Connection> connectionPool = new GenericObjectPool<Connection>();
		connectionPool.setMinIdle(minIdle);
		connectionPool.setMaxActive(maxActive);
		ConnectionFactory connFactory = new DriverManagerConnectionFactory(connectUri, username, password);
		factory = new PoolableConnectionFactory(connFactory, connectionPool, null, null, false, true);
		dSource = new PoolingDataSource(factory.getPool());		
		
		System.out.println("password : " + password);
	}
	
	public Connection getConnection() throws SQLException {
		System.out.println("connection state : " + factory.getPool().getNumActive() + "/" + factory.getPool().getNumIdle());
		return dSource.getConnection();
	}


}
